VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "CEventHandler2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'''
''' This class module can trap events for any workbook you specify.
''' However, you must locate and open the workbook whose events you
''' want to trap, create an instance of this class in a public variable
''' and set the Book property procedure below to the workbook whose
''' events you want to trap in order to use the class.
'''
Option Explicit

''' This variable traps all events generated by the workbook specified by the Book property.
Private WithEvents wkbEventSource As Excel.Workbook


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' This property procedure is used to specify which workbook's
''' events this class module should trap.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Property Set Book(ByRef wkbNewValue As Excel.Workbook)
    Set wkbEventSource = wkbNewValue
End Property


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' These are Class-specific Events ''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Class_Terminate()
    ''' Destroy the WithEvents variable when the class is destroyed.
    Set wkbEventSource = Nothing
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Workbook Events are Trapped in This Section ''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub wkbEventSource_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "The Workbook_BeforeSave Event Fired.", vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_NewSheet(ByVal Sh As Object)
    MsgBox "The " & TypeName(Sh) & " " & Sh.Name & " was added.", vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "The Workbook_SheetBeforeRightClick Event Fired." & vbLf & "Sheet: " & Sh.Name & vbLf & "Range: " & Target.Address, vbInformation, wkbEventSource.Name
    Cancel = True
End Sub


Private Sub wkbEventSource_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "The Workbook_SheetChange Event Fired." & vbLf & "Sheet: " & Sh.Name & vbLf & "Range: " & Target.Address, vbInformation, wkbEventSource.Name
End Sub


Private Sub wkbEventSource_BeforeClose(Cancel As Boolean)
    ''' Be sure to destroy the WithEvents variable before the workbook it references is closed.
    ''' In a real-world application, doing this properly can be a complicated task. For the sake
    ''' of simplicity we'll just do this unconditionally in the BeforClose event.
    Set wkbEventSource = Nothing
End Sub

''' NOTE: You can trap as many additional events as you like.
